package com.mhsoft.pentamanager.services;

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;

/**
 *
 * @author MH
 */
public class DBConnectionManagerNew {

    public static final int CONN_TYPE_ACCESS=1;
    public static final int CONN_TYPE_MYSQL=2;
    
    private String[] NumberIndocators=new String[]{"ID", "No"};
    
    private Connection conn;
    private Statement st;
    private String errMsg = "No error";
    private String odbcOrDBName;
    private int DBType;

    public DBConnectionManagerNew(int DBType, String odbcOrDBName, String Username, String Password) {
        this.DBType=DBType;
        this.odbcOrDBName=odbcOrDBName;
        try {
            if (DBType == CONN_TYPE_ACCESS) {
                Class.forName("sun.jdbc.odbc.JdbcOdbcDriver").newInstance();
                conn = DriverManager.getConnection("jdbc:odbc:" + odbcOrDBName, Username, Password);
                st = conn.createStatement();
            } else if (DBType == CONN_TYPE_MYSQL) {
                String unicode= "?useUnicode=yes&characterEncoding=UTF-8";
                Class.forName("com.mysql.jdbc.Driver").newInstance();
                conn=DriverManager.getConnection("jdbc:mysql://localhost:3306/"+odbcOrDBName+unicode,Username,Password);
                st = conn.createStatement();
            }
        } catch (Exception e) {
            System.out.println(e.getMessage());
            errMsg = e.getMessage();
        }
    }

    public DBConnectionManagerNew(int DBType, String odbcOrDBName) {
        try {
            if (DBType == CONN_TYPE_ACCESS) {
                Class.forName("sun.jdbc.odbc.JdbcOdbcDriver").newInstance();
                conn = DriverManager.getConnection("jdbc:odbc:" + odbcOrDBName);
                st = conn.createStatement();
            } else if (DBType == CONN_TYPE_MYSQL) {
                Class.forName("com.mysql.jdbc.Driver").newInstance();
                conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/" + odbcOrDBName);
                st = conn.createStatement();
            }
        } catch (Exception e) {
            System.out.println(e.getMessage());
            errMsg = e.getMessage();
        }
    }

    String getErrorMessage() {
        return errMsg;

    }

    public void insert(String tableName, HashMap<String, String> data) {
        StringBuilder fields=new StringBuilder();
        StringBuilder values=new StringBuilder();
        Object[] keys = data.keySet().toArray();
        for (int i = 0; i < keys.length; i++) {
            String key=keys[i].toString();
            String value=data.get(keys[i]);
            fields.append(keys[i]).append(",");
            if(isText(key)) values.append("'").append(value).append("'").append(",");
            else values.append(value).append(",");
        }
        if(DBType==CONN_TYPE_MYSQL)tableName=odbcOrDBName+"."+ tableName;
        StringBuilder insertStatment =new StringBuilder("insert into "+ tableName + " (");
        insertStatment.append(fields.substring(0, fields.length()-1));
        insertStatment.append(") values(");
        insertStatment.append(values.substring(0, values.length()-1));
        insertStatment.append(")");

        System.out.println(insertStatment);
        
        try {
            st.executeUpdate(insertStatment.toString());
        } catch (Exception e) {
            System.out.println(e.getMessage());
            errMsg = e.getMessage();
        }
    }
    
    public void delete(String tableName, String conditions) {
        String deleteStatment = "delete from " + tableName + " where " + conditions;
        try {
            st.executeUpdate(deleteStatment);
        } catch (Exception e) {
            System.out.println(e.getMessage());
            errMsg = e.getMessage();
        }
    }

    public void update(String tableName, HashMap<String, String> data) {
        StringBuilder updates=new StringBuilder();
        Object[] keys = data.keySet().toArray();
        for (int i = 0; i < keys.length; i++) {
            String key=keys[i].toString();
            String value=data.get(keys[i]);
            updates.append(key).append("=");
            if(isText(key)) updates.append("'").append(value).append("'").append(",");
            else updates.append(value).append(",");
        }
        StringBuilder updateStatment =new StringBuilder("update " + tableName + " set ");
        updateStatment.append(updates.substring(0, updates.length()-1));

        System.out.println(updateStatment);
        
        try {
            st.executeUpdate(updateStatment.toString());
        } catch (Exception e) {
            System.out.println(e.getMessage());
            errMsg = e.getMessage();
        }
    }
    public void update(String tableName, HashMap<String, String> data, String conditions) {
        StringBuilder updates=new StringBuilder();
        Object[] keys = data.keySet().toArray();
        for (int i = 0; i < keys.length; i++) {
            String key=keys[i].toString();
            String value=data.get(keys[i]);
            updates.append(key).append("=");
            if(isText(key)) updates.append("'").append(value).append("'").append(",");
            else updates.append(value).append(",");
        }
        StringBuilder updateStatment =new StringBuilder("update " + tableName + " set ");
        updateStatment.append(updates.substring(0, updates.length()-1));
        updateStatment.append(" where ").append(conditions);
        
        System.out.println(updateStatment);
        try {
            st.executeUpdate(updateStatment.toString());
        } catch (Exception e) {
            System.out.println(e.getMessage());
            errMsg = e.getMessage();
        }
    }

    public ArrayList<HashMap<String, String>> select(String[] tablesNames, String[] fields, String conditions) {
        ResultSet rs;
        StringBuilder selectStatment = new StringBuilder("select ");
        StringBuilder fieldsBuilder=new StringBuilder();
        StringBuilder tablesBuilder=new StringBuilder();
        
        for(int i=0;i<fields.length;i++)fieldsBuilder.append(fields[i]).append(",");
        selectStatment.append(fieldsBuilder.substring(0, fieldsBuilder.length()-1));
        
        selectStatment.append(" from ");
        
        for(int i=0;i<tablesNames.length;i++){
            if(DBType==CONN_TYPE_MYSQL)tablesNames[i]=odbcOrDBName+"."+tablesNames[i];
            tablesBuilder.append(tablesNames[i]).append(",");
        }
        selectStatment.append(tablesBuilder.substring(0, tablesBuilder.length()-1));
        
        selectStatment.append(" where ").append(conditions);
        
        System.out.println(selectStatment);
        try {
            rs = st.executeQuery(selectStatment.toString());
            return getSelectResult(rs, fields);
        } catch (Exception e) {
            System.out.println(e.getMessage());
            errMsg = e.getMessage();
            return new ArrayList<HashMap<String, String>>();
        }
        //return rs;  
    }
    
    public ArrayList<HashMap<String, String>> select(String[] tablesNames, String[] fields) {
        ResultSet rs;
        StringBuilder selectStatment = new StringBuilder("select ");
        StringBuilder fieldsBuilder=new StringBuilder();
        StringBuilder tablesBuilder=new StringBuilder();
        
        for(int i=0;i<fields.length;i++)fieldsBuilder.append(fields[i]).append(",");
        selectStatment.append(fieldsBuilder.substring(0, fieldsBuilder.length()-1));
        
        selectStatment.append(" from ");
        
        for(int i=0;i<tablesNames.length;i++){
            if(DBType==CONN_TYPE_MYSQL)tablesNames[i]=odbcOrDBName+"."+tablesNames[i];
            tablesBuilder.append(tablesNames[i]).append(",");
        }
        selectStatment.append(tablesBuilder.substring(0, tablesBuilder.length()-1));
        
        System.out.println(selectStatment);
        try {
            rs = st.executeQuery(selectStatment.toString());
            return getSelectResult(rs, fields);
        } catch (Exception e) {
            System.out.println(e.toString());
            errMsg = e.getMessage();
            return new ArrayList<HashMap<String, String>>();
        } 
    }

    public ResultSet selector(String selectStatment) {
        ResultSet rs;
        try {
            rs = st.executeQuery(selectStatment);
            return rs;
        } catch (Exception e) {
            System.out.println(e.getMessage());
            errMsg = e.getMessage();
            return null;

        }
    }
    
    private boolean isText(String key){
        boolean isText=true;
        for(int j=0;j<NumberIndocators.length;j++){
            if(key.endsWith(NumberIndocators[j]))isText=false;
        }
        return isText;
    }
    
    private ArrayList<HashMap<String, String>> getSelectResult(ResultSet rs, String[] fields) throws SQLException{
        ArrayList<HashMap<String, String>> list=new ArrayList<HashMap<String, String>>();
        while(rs.next()){
            HashMap<String, String> item=new HashMap<String, String>();
            for(int i=0;i<fields.length;i++){
                if(isText(fields[i]))item.put(fields[i], rs.getString(fields[i]));
                else item.put(fields[i], rs.getInt(fields[i])+"");
            }
            list.add(item);
        }
        return list;
    }
}
